﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using PSI.Models;
using System.Text;
using PSI.Helper;

namespace PSI.Services {
    public class BaseCodeMetadataColumnService:PSIBaseService {

        public List<BaseCodeColumnMatedata> GetMetadataColumnItem(string id) {
            var result = new List<BaseCodeColumnMatedata>();

            var str = new StringBuilder("select basecode_id,column_id,column_name,column_title,column_length,defualt_value,column_type,display_fmt,is_visible,show_order from  psi_basecode_column_metadata where basecode_id=@id");

            var p = CreateDbParameters();

            p.AddWithValue("id",id);

            Query(str.ToString(), p, ready => {

                result.Add(new BaseCodeColumnMatedata {
                    BasecodeId=ready.GetString(ready.GetOrdinal("basecode_id")),
                    ColumnId = ready.GetString(ready.GetOrdinal("column_id")),
                    ColumnName = ready.GetString(ready.GetOrdinal("column_name")),
                    ColumnTitle = ready.GetString(ready.GetOrdinal("column_title")),
                    ColumnLength = ready.GetInt32(ready.GetOrdinal("column_length")),
                    DefualtValue = ready.GetString(ready.GetOrdinal("defualt_value")),
                    ColumnType = ready.GetString(ready.GetOrdinal("column_type")),
                    DisplayFmt = ready.GetString(ready.GetOrdinal("display_fmt")),
                    IsVisible = ready.GetInt32(ready.GetOrdinal("is_visible")),
                    ShowOrder = ready.GetInt32(ready.GetOrdinal("show_order"))
                });

            });

            return result;
          
        }

        public BaseCodeColumnMatedata GetSing(string id) {
            var result = new BaseCodeColumnMatedata();

            var str = new StringBuilder("select basecode_id,column_id,column_name,column_title,column_length,defualt_value,column_type,display_fmt,is_visible,show_order from psi_basecode_column_metadata column_id=@id");

            var p = CreateDbParameters();

            p.AddWithValue("id", id);

            Query(str.ToString(), p, ready => {
                result = new BaseCodeColumnMatedata {
                    BasecodeId = ready.GetString(ready.GetOrdinal("basecode_id")),
                    ColumnId = ready.GetString(ready.GetOrdinal("column_id")),
                    ColumnName = ready.GetString(ready.GetOrdinal("column_name")),
                    ColumnTitle = ready.GetString(ready.GetOrdinal("column_title")),
                    ColumnLength = ready.GetInt32(ready.GetOrdinal("column_length")),
                    DefualtValue = ready.GetString(ready.GetOrdinal("defualt_value")),
                    ColumnType = ready.GetString(ready.GetOrdinal("column_type")),
                    DisplayFmt = ready.GetString(ready.GetOrdinal("display_fmt")),
                    IsVisible = ready.GetInt32(ready.GetOrdinal("is_visible")),
                    ShowOrder = ready.GetInt32(ready.GetOrdinal("show_order"))
                };
               
            });
            return result;
          
        }

        public int GetMaxShowOrder() {

            var num=0;

            var str = new StringBuilder("select top 1 show_order from psi_basecode_column_metadata order by show_order desc");

            Query(str.ToString(), ready => {

                num = ready.GetInt32(0);
            });


            return num+1; 
        }

        public Result AddMetadataColumn(BaseCodeColumnMatedata model) {

            var Detectionstr = new StringBuilder("select count(*) from psi_basecode_column_metadata where column_name=@cn and basecode_id=@baseid");

            var p = CreateDbParameters();

            p.AddWithValue("cn", model.ColumnName);
            p.AddWithValue("baseid", model.BasecodeId);

            int result=QueryCount(Detectionstr.ToString(), p);

            if (result > 0) return new Result { Success = false, Messgae = "内部原因,该表已经存在！" };



            WithTransaction(() => {
              

                if (model.ShowOrder == 0) {
                    model.ShowOrder = GetMaxShowOrder();     
                }

                //添加 新增 字段信息
                var insert = new StringBuilder("insert psi_basecode_column_metadata values (@column_id,@basecode_id,@column_title,@column_name,@column_type,@column_length,@defualt_value,@show_order,@is_visible,@display_fmt)");

                p = CreateDbParameters();

                p.AddWithValue("column_id", Guid.NewGuid());
                p.AddWithValue("basecode_id",model.BasecodeId);
                p.AddWithValue("column_title", model.ColumnTitle == null ? "" : model.ColumnTitle);
                p.AddWithValue("column_name",model.ColumnName);
                p.AddWithValue("column_type",model.ColumnType);
                p.AddWithValue("column_length",model.ColumnLength);
                p.AddWithValue("defualt_value", model.DefualtValue == null ? "" : model.DefualtValue);
                p.AddWithValue("show_order",model.ShowOrder);
                p.AddWithValue("is_visible", model.IsVisible);
                p.AddWithValue("display_fmt", model.DisplayFmt == null ? "" : model.DisplayFmt);

                Execute(insert.ToString(),p);

                //得到要插入表的名字

                var metadata=new List<BaseCodeMetadata>();
                
                var gettablename=new StringBuilder("select * from psi_basecode_metadata where basecode_id=@id");
                 
                p = CreateDbParameters();

                p.AddWithValue("id",model.BasecodeId);

                Query(gettablename.ToString(),p,ready=>{
                
                    metadata.Add(new BaseCodeMetadata{
                    
                        TableName=ready.GetString(ready.GetOrdinal("table_name"))
                    
                    });
                
                });

                //得到字段类型

                var columntype=model.ColumnType;

                if(model.ColumnType=="varchar")
                {
                  if(model.ColumnLength>0)
                  {
                    columntype+="("+model.ColumnLength+")";
                  }
                  else
                  {
                    columntype+="(50)";
                  }
                }


              
                //给指定表 添加字段
                var alter = new StringBuilder("alter table "+metadata[0].TableName+" add "+model.ColumnName+" "+columntype+" ");
                Execute(alter.ToString());

            });

            return new Result { Success = true };
       
        }

    }
}